package com.zhuoshi.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zhuoshi.bean.PurchaseBean;
import com.zhuoshi.utils.DbHelper;

/**
 * 采购合同 Dao  数据处理层
 * @author Administrator
 *
 */
public class PurchaseDao {
	// 添加
		public int add(PurchaseBean bean) {
			Connection conn = null;
			PreparedStatement pstat = null;
			int rows = 0;
			try {
				conn = DbHelper.getConnection();
				StringBuffer sql = new StringBuffer();
				sql.append(
						"insert into purchase (numerical,sqarename,modeltype,unit,price,totalcount,supplier,purchasedate,totalmoney) values");
				sql.append(" (?,?,?,?,?,?,?,?,?)");
				pstat = conn.prepareStatement(sql.toString());
				pstat.setInt(1, bean.getNumerical());
				pstat.setString(2, bean.getSqarename());
				pstat.setString(3, bean.getModeltype());
				pstat.setString(4, bean.getUnit());
				pstat.setDouble(5, bean.getPrice());
				pstat.setInt(6, bean.getTotalcount());
				pstat.setString(7, bean.getSupplier());
				pstat.setDate(8, bean.getPurchasedate());
				pstat.setDouble(9, bean.getTotalmoney());
				
				rows = pstat.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, null);
			}

			return rows;
		}

		// 修改
		public int update(PurchaseBean bean) {
			Connection conn = null;
			PreparedStatement pstat = null;
			int rows = 0;
			try {
				conn = DbHelper.getConnection();
				StringBuffer sql = new StringBuffer();
				sql.append("update purchase set ");
				sql.append("numerical=?,");
				sql.append("sqarename=?,");
				sql.append("modeltype=?,");
				sql.append("unit=?,");
				sql.append("price=?,");
				sql.append("totalcount=?,");
				sql.append("supplier=?,");
				sql.append("purchasedate=?,");
				sql.append("totalmoney=?");
				sql.append(" where id=?");
				pstat = conn.prepareStatement(sql.toString());
				pstat.setInt(1, bean.getNumerical());
				pstat.setString(2, bean.getSqarename());
				pstat.setString(3, bean.getModeltype());
				pstat.setString(4, bean.getUnit());
				pstat.setDouble(5, bean.getPrice());
				pstat.setInt(6, bean.getTotalcount());
				pstat.setString(7, bean.getSupplier());
				pstat.setDate(8,bean.getPurchasedate());
				pstat.setDouble(9,bean.getTotalmoney());
				
				pstat.setInt(10, bean.getId());
				rows = pstat.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, null);
			}

			return rows;
		}

		// 删除
		public int delete(int id) {
			Connection conn = null;
			PreparedStatement pstat = null;
			int rows = 0;
			try {
				conn = DbHelper.getConnection();
				StringBuffer sql = new StringBuffer();
				sql.append("delete from purchase where id=?");
				pstat = conn.prepareStatement(sql.toString());
				pstat.setInt(1, id);
				rows = pstat.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, null);
			}

			return rows;
		}

		/**
		 * 通过id查询 获取一个数据实体
		 * 
		 * @param id
		 *            自增ID
		 * @return
		 */
		public PurchaseBean getModel(int id) {
			Connection conn = null;
			PreparedStatement pstat = null;
			ResultSet rs = null;
			PurchaseBean bean = null;
			try {
				conn = DbHelper.getConnection();
				StringBuffer sql = new StringBuffer();
				sql.append("select * from purchase where id=?");
				pstat = conn.prepareStatement(sql.toString());
				pstat.setInt(1, id);
				rs = pstat.executeQuery();
				while (rs.next()) {
					bean = new PurchaseBean();
					bean.setId(rs.getInt("id"));
					bean.setNumerical(rs.getInt("numerical"));
					bean.setSqarename(rs.getString("sqarename"));
					bean.setModeltype(rs.getString("modeltype"));
					bean.setUnit(rs.getString("unit"));
					bean.setPrice(rs.getDouble("price"));
					bean.setTotalcount(rs.getInt("totalcount"));
					bean.setSupplier(rs.getString("supplier"));
					bean.setPurchasedate(rs.getDate("purchasedate"));
					bean.setTotalmoney(rs.getDouble("totalmoney"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, rs);
			}
			return bean;
		}

		/**
		 * 查询多条 List
		 * 
		 * @param bean
		 *            条件 如果没有条件 传入null 查询所有
		 * @return
		 */
		public List<PurchaseBean> getList(PurchaseBean bean, String startDate, String endDate) {
			Connection conn = null;
			PreparedStatement pstat = null;
			ResultSet rs = null;
			List<PurchaseBean> list = new ArrayList<PurchaseBean>();
			try {
				conn = DbHelper.getConnection();
				// ---===================构建查询条件=================---//
				StringBuffer sql = new StringBuffer();
				sql.append("select * from purchase where 1=1");
				// if(bean!=null){

				if (null != bean.getNumerical()) {// 合同数字编号
					sql.append(" and numerical like '%" + bean.getNumerical() + "%'");
				}
				if (!"".equals(startDate) && startDate != null) {
					sql.append(" and purchasedate>='" + startDate + "'");
				}

				if (!"".equals(endDate) && endDate != null) {
					sql.append(" and purchasedate<='" + endDate + "'");
				}

				// }
				// ---===================/构建查询条件=================---//
				pstat = conn.prepareStatement(sql.toString());
				// System.out.println(sql);
				rs = pstat.executeQuery();
				while (rs.next()) {
					PurchaseBean cBean = new PurchaseBean();
					cBean.setId(rs.getInt("id"));
					cBean.setNumerical(rs.getInt("numerical"));
					cBean.setSqarename(rs.getString("sqarename"));
					cBean.setModeltype(rs.getString("modeltype"));
					cBean.setUnit(rs.getString("unit"));
					cBean.setPrice(rs.getDouble("price"));
					cBean.setTotalcount(rs.getInt("totalcount"));
					cBean.setSupplier(rs.getString("supplier"));
					cBean.setPurchasedate(rs.getDate("purchasedate"));
					cBean.setTotalmoney(rs.getDouble("totalmoney"));
					list.add(cBean);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, rs);
			}
			return list;
		}
		
		public List<PurchaseBean> getList() {
			Connection conn = null;
			PreparedStatement pstat = null;
			ResultSet rs = null;
			List<PurchaseBean> list = new ArrayList<PurchaseBean>();
			try {
				conn = DbHelper.getConnection();
				// ---===================构建查询条件=================---//
				StringBuffer sql = new StringBuffer();
				sql.append("select * from purchase where 1=1");
				// if(bean!=null){

				

				// }
				// ---===================/构建查询条件=================---//
				pstat = conn.prepareStatement(sql.toString());
				// System.out.println(sql);
				rs = pstat.executeQuery();
				while (rs.next()) {
					PurchaseBean cBean = new PurchaseBean();
					cBean.setId(rs.getInt("id"));
					cBean.setNumerical(rs.getInt("numerical"));
					cBean.setSqarename(rs.getString("sqarename"));
					cBean.setModeltype(rs.getString("modeltype"));
					cBean.setUnit(rs.getString("unit"));
					cBean.setPrice(rs.getDouble("price"));
					cBean.setTotalcount(rs.getInt("totalcount"));
					cBean.setSupplier(rs.getString("supplier"));
					cBean.setPurchasedate(rs.getDate("purchasedate"));
					cBean.setTotalmoney(rs.getDouble("totalmoney"));
					list.add(cBean);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbHelper.dispose(conn, pstat, rs);
			}
			return list;
		}
		
}
